------------------------------------------------------------------------------------
--  PROCEDURE NAME: dbo.UpsertDatabase    
--     DESCRIPTION: Inserts or updates a record in the Database table
------------------------------------------------------------------------------------
--   REFERENCED BY: Upsert-Database.ps1
--    PROJECT NAME: iSQLPS
------------------------------------------------------------------------------------
--  CHANGE HISTORY:
-- DATE        MODIFIED   DESCRIPTION   
------------------------------------------------------------------------------------
-- 08.10.2010  SYoung     Initial creation
------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[UpsertDatabase]
       @ServerSQLID int ,
       @DatabaseName varchar(128),
       @Collation varchar(128),
       @CompatibilityLevel char(12),
       @DBCreateDate smalldatetime,
       @RecoveryModel char(10),
       @Active tinyint = 1
              
AS

SET NOCOUNT ON

DECLARE @ERRORCODE int, 
        @ERRMSG varchar(128)

IF NOT EXISTS (SELECT 'TRUE'
                 FROM dbo.[Database]
                WHERE ServerSQLID = @ServerSQLID AND
                      DatabaseName = @DatabaseName)
   BEGIN
   INSERT dbo.[Database](
          ServerSQLID
        , DatabaseName
        , Collation
        , CompatibilityLevel
        , DBCreateDate
        , RecoveryModel
        , CreateDate
        , UpdateDate
        , Active)
   VALUES (
          @ServerSQLID
        , @DatabaseName
        , @Collation
        , @CompatibilityLevel
        , @DBCreateDate
        , @RecoveryModel
        , GETDATE()
        , GETDATE()
        , @Active)
END
ELSE
   BEGIN
   IF EXISTS (SELECT 'TRUE'
                FROM dbo.[Database]
               WHERE ServerSQLID = @ServerSQLID AND
                     DatabaseName = @DatabaseName AND
                     (Collation != @Collation OR
                     CompatibilityLevel != @CompatibilityLevel OR
                     DBCreateDate != @DBCreateDate OR
                     RecoveryModel != @RecoveryModel))
      BEGIN 
      UPDATE dbo.[Database]
         SET Collation = @Collation,
             CompatibilityLevel = @CompatibilityLevel,
             DBCreateDate = @DBCreateDate,
             RecoveryModel = @RecoveryModel,
             UpdateDate = GETDATE()
       WHERE ServerSQLID = @ServerSQLID AND
             DatabaseName = @DatabaseName
   END
   ELSE
      BEGIN
      RETURN
   END
END

SET @ERRORCODE = @@ERROR

IF @ERRORCODE <> 0 
   BEGIN
   SET @ERRMSG = 'Upsert failed - ' + OBJECT_NAME(@@PROCID)
   SET @ERRMSG = @ERRMSG + ' Error Code: ' + RTRIM(CONVERT(CHAR, @ERRORCODE)) 
   RAISERROR (@ERRMSG, 16, 1)
   RETURN (-1)
END

GO

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Inserts or updates a record in the Database table.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'UpsertDatabase'
GO

